package Colecciones;

import Modelo.EjecutaConsulta;
import java.io.PrintStream;
import java.util.Vector;

public class datosParaListas
{
  public static Vector getCiudades(String p_descripcion, int p_reg)
  {
    Vector datos = new Vector();
    try
    {
      String consulta = " SELECT CODIGO, NOMBRE, DEP_CODIGO  FROM PS_PUJ_CIUDADES_WEB  WHERE DESCRIPTORES.QUITAR_TILDES(NOMBRE) LIKE DESCRIPTORES.QUITAR_TILDES('%" + p_descripcion + "%')";

      if (p_reg > 0) {
        consulta = consulta + " AND ROWNUM <= " + p_reg + " ORDER BY 2";
      }
      else
        consulta = consulta + " ORDER BY 2";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de datosParaListas.getCiudades " + e.toString());
    }

    return datos;
  }

  public static Vector getCiudadesCompleto(String p_descripcion, int p_reg)
  {
    Vector datos = new Vector();
    try
    {
      String consulta = " SELECT C.NOMBRE||','||T.DESCR||','||C.DESCR CIUDAD FROM PS_PUJ_CIUDADES c, PS_STATE_NAMES_TBL t,     PS_COUNTRYTBL_LANG c WHERE C.DEP_CODIGO= T.STATE AND T.COUNTRY= C.COUNTRY AND C.NOMBRE NOT LIKE 'DESCONOCIDA' AND DESCRIPTORES.QUITAR_TILDES(NOMBRE) LIKE DESCRIPTORES.QUITAR_TILDES('%" + p_descripcion + "%')";

      if (p_reg > 0) {
        consulta = consulta + " AND ROWNUM <= " + p_reg + "  ORDER BY c.descr,t.descr,c.nombre";
      }
      else
        consulta = consulta + " ORDER BY c.descr,t.descr,c.nombre";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de datosParaListas.getCiudadesCompleto " + e.toString());
    }

    return datos;
  }

  public static Vector getCiudadesColegios(String p_descripcion, int p_reg)
  {
    Vector datos = new Vector();
    try
    {
      String consulta = " SELECT DISTINCT COUNTRY PAIS , CITY CIUDAD  FROM PS_EXT_ORG_TBL   WHERE  EXT_ORG_TYPE ='SCHL'  AND LS_SCHOOL_TYPE ='SEC'  AND CITY NOT LIKE ' '  AND DESCRIPTORES.QUITAR_TILDES(CITY) LIKE DESCRIPTORES.QUITAR_TILDES('%" + p_descripcion + "%')";

      if (p_reg > 0) {
        consulta = consulta + " AND ROWNUM <= " + p_reg + " ORDER BY 2";
      }
      else
        consulta = consulta + " ORDER BY 2";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de datosParaListas.getCiudadesColegios " + e.toString());
    }

    return datos;
  }

  public static Vector getBarrios(String p_ciudad, String p_descripcion, int p_reg)
  {
    Vector datos = new Vector();
    try
    {
      String consulta = " SELECT CODIGO, DESCRIPCION  FROM PS_PUJ_BARRIOS  WHERE DESCRIPTORES.QUITAR_TILDES(DESCRIPCION) LIKE DESCRIPTORES.QUITAR_TILDES('%" + p_descripcion + "%')" + " AND COD_CIUDAD='" + p_ciudad + "'";

      if (p_reg > 0) {
        consulta = consulta + " AND ROWNUM<= " + p_reg + " ORDER BY 2";
      }
      else
      {
        consulta = consulta + " ORDER BY 2";
      }

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de datosParaListas.getBarrios " + e.toString());
    }

    return datos;
  }

  public static Vector getTiposDocumentos()
  {
    Vector datos = new Vector();
    try
    {
      String consulta = " SELECT NATIONAL_ID_TYPE TIPO, DESCR DESCRIPCION FROM PS_NID_TYPE_TBL  WHERE COUNTRY='COL'";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de datosParaListas.getTiposDocumwentos " + e.toString());
    }

    return datos;
  }

  public static Vector getCarreras(String p_grado, String p_descripcion)
  {
    Vector datos = new Vector();
    try
    {
      String consulta = " SELECT A.ACAD_PROG, A.DESCR DESCRIPCION  FROM PS_ACAD_PROG_TBL A  WHERE A.ACAD_CAREER= '" + p_grado + "' " + " AND   A.EFF_STATUS ='A' " + " AND A.EFFDT =(SELECT MAX(B.EFFDT) " + "  FROM PS_ACAD_PROG_TBL B " + " WHERE A.ACAD_CAREER=B.ACAD_CAREER " + " AND   A.ACAD_PROG = B.ACAD_PROG " + " AND   A.INSTITUTION = B.INSTITUTION " + " AND   B.EFFDT<=SYSDATE) " + " AND DESCRIPTORES.QUITAR_TILDES(A.DESCR) LIKE DESCRIPTORES.QUITAR_TILDES('%" + p_descripcion + "%')" + " ORDER BY 2 ";

      System.out.println(consulta);
      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de datosParaListas.getCarreras " + e.toString());
    }

    return datos;
  }

  public static Vector getUniversidades(String p_descripcion, int p_reg)
  {
    Vector datos = new Vector();
    try
    {
      String consulta = " SELECT EX.EXT_ORG_ID CODIGO, EX.DESCR50 DESCRIPCION FROM PS_EXT_ORG_TBL EX  WHERE EX.EXT_ORG_TYPE='SCHL'  AND   EX.LS_SCHOOL_TYPE='UNI'  AND   EX.EFF_STATUS='A'  AND   EX.EFFDT = (SELECT MAX(EX2.EFFDT)  FROM PS_EXT_ORG_TBL EX2  WHERE EX.EXT_ORG_ID =EX2.EXT_ORG_ID  AND   EX.EXT_ORG_TYPE=EX2.EXT_ORG_TYPE  AND   EX.LS_SCHOOL_TYPE=EX2.LS_SCHOOL_TYPE  AND   EX2.EFFDT<=SYSDATE)  AND DESCRIPTORES.QUITAR_TILDES(EX.DESCR50) LIKE DESCRIPTORES.QUITAR_TILDES('%" + p_descripcion + "%')";

      if (p_reg > 0) {
        consulta = consulta + " AND ROWNUM <= " + p_reg + " ORDER BY DESCR50 ";
      }
      else
        consulta = consulta + " ORDER BY DESCR50 ";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de datosParaListas.getUniversidades " + e.toString());
    }

    return datos;
  }

  public static Vector getColegios(String p_descripcion, int p_reg)
  {
    Vector datos = new Vector();
    try
    {
      String consulta = " SELECT EX.EXT_ORG_ID CODIGO, EX.DESCR50 DESCRIPCION,DESCRSHORT CODIGO_ICFES, X.XLATLONGNAME TIPO_INST FROM PS_EXT_ORG_TBL EX  , XLATTABLE X WHERE EX.EXT_ORG_TYPE='SCHL'  AND   EX.LS_SCHOOL_TYPE='SEC'  AND   EX.EFF_STATUS='A'  AND   EX.EFFDT = (SELECT MAX(EX2.EFFDT)  FROM PS_EXT_ORG_TBL EX2  WHERE EX.EXT_ORG_ID =EX2.EXT_ORG_ID  AND   EX.EXT_ORG_TYPE=EX2.EXT_ORG_TYPE  AND   EX.LS_SCHOOL_TYPE=EX2.LS_SCHOOL_TYPE  AND   EX2.EFFDT<=SYSDATE)  AND X.FIELDNAME='PROPRIETORSHIP'  AND X.LANGUAGE_CD='ESP'  AND EX.PROPRIETORSHIP= X.FIELDVALUE AND DESCRIPTORES.QUITAR_TILDES(EX.DESCR50) LIKE DESCRIPTORES.QUITAR_TILDES('%" + p_descripcion + "%')";

      if (p_reg > 0) {
        consulta = consulta + " AND ROWNUM <= " + p_reg + " ORDER BY DESCR50 ";
      }
      else
        consulta = consulta + " ORDER BY DESCR50 ";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de datosParaListas.getColegios " + e.toString());
    }

    return datos;
  }

  public static Vector getColegiosxCiudad(String p_ciudad, String p_descripcion, int p_reg)
  {
    Vector datos = new Vector();
    try
    {
      String consulta = " SELECT EX.EXT_ORG_ID CODIGO, EX.DESCR50 DESCRIPCION,DESCRSHORT CODIGO_ICFES, X.XLATLONGNAME TIPO_INST,EX.PROPRIETORSHIP FROM PS_EXT_ORG_TBL EX  , XLATTABLE X WHERE EX.EXT_ORG_TYPE='SCHL'  AND   EX.LS_SCHOOL_TYPE='SEC'  AND   EX.EFF_STATUS='A'  AND   EX.EFFDT = (SELECT MAX(EX2.EFFDT)  FROM PS_EXT_ORG_TBL EX2  WHERE EX.EXT_ORG_ID =EX2.EXT_ORG_ID  AND   EX.EXT_ORG_TYPE=EX2.EXT_ORG_TYPE  AND   EX.LS_SCHOOL_TYPE=EX2.LS_SCHOOL_TYPE  AND   EX2.EFFDT<=SYSDATE)  AND X.FIELDNAME='PROPRIETORSHIP'  AND X.LANGUAGE_CD='ESP'  AND EX.PROPRIETORSHIP= X.FIELDVALUE AND DESCRIPTORES.QUITAR_TILDES(EX.DESCR50) LIKE DESCRIPTORES.QUITAR_TILDES('%" + p_descripcion + "%')" + " AND DESCRIPTORES.QUITAR_TILDES(EX.CITY) LIKE DESCRIPTORES.QUITAR_TILDES('%" + p_ciudad + "%')";

      if (p_reg > 0) {
        consulta = consulta + " AND ROWNUM <= " + p_reg + " ORDER BY DESCR50 ";
      }
      else
        consulta = consulta + " ORDER BY DESCR50 ";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de datosParaListas.getColegios " + e.toString());
    }

    return datos;
  }

  public static Vector getEmpresas(String p_descripcion, int p_reg)
  {
    Vector datos = new Vector();
    try
    {
      String consulta = " SELECT EX.EXT_ORG_ID CODIGO, EX.DESCR50 DESCRIPCION,DESCRSHORT, X.XLATLONGNAME TIPO_INST FROM PS_EXT_ORG_TBL EX      ,XLATTABLE X WHERE EX.EXT_ORG_TYPE='BUSN'  AND   EX.EFF_STATUS='A'  AND   EX.EFFDT = (SELECT MAX(EX2.EFFDT)  FROM PS_EXT_ORG_TBL EX2  WHERE EX.EXT_ORG_ID =EX2.EXT_ORG_ID  AND   EX.EXT_ORG_TYPE=EX2.EXT_ORG_TYPE  AND   EX.LS_SCHOOL_TYPE=EX2.LS_SCHOOL_TYPE  AND   EX2.EFFDT<=SYSDATE)  AND X.FIELDNAME='PROPRIETORSHIP'  AND X.LANGUAGE_CD='ESP'  AND EX.PROPRIETORSHIP= X.FIELDVALUE AND DESCRIPTORES.QUITAR_TILDES(EX.DESCR50) LIKE DESCRIPTORES.QUITAR_TILDES('%" + p_descripcion + "%')";

      if (p_reg > 0) {
        consulta = consulta + " AND ROWNUM <= " + p_reg + " ORDER BY DESCR50 ";
      }
      else {
        consulta = consulta + " ORDER BY DESCR50 ";
      }

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de datosParaListas.getEmpresas " + e.toString());
    }

    return datos;
  }

  public static Vector getTitulosAcademicos(String p_descripcion, int p_reg)
  {
    Vector datos = new Vector();
    try
    {
      String consulta = " SELECT DEGREE CODIGO, DESCRFORMAL TITULO  FROM PS_DEGREE_TBL A  WHERE A.EFF_STATUS='A'  AND  A.EFFDT = (SELECT MAX(B.EFFDT)  FROM PS_DEGREE_TBL B  WHERE A.DEGREE = B.DEGREE  AND   B.EFFDT <= SYSDATE)  AND DESCRIPTORES.QUITAR_TILDES(A.DESCRFORMAL) LIKE DESCRIPTORES.QUITAR_TILDES('%" + p_descripcion + "%')";

      if (p_reg > 0) {
        consulta = consulta + " AND ROWNUM <= " + p_reg + " ORDER BY DESCRFORMAL ";
      }
      else
        consulta = consulta + " ORDER BY DESCRFORMAL ";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de datosParaListas.getUniversidades " + e.toString());
    }

    return datos;
  }

  public static Vector getOtrosDatos(String p_nombre_campo)
  {
    Vector datos = new Vector();
    try
    {
      String consulta = " SELECT FIELDVALUE CONSECUTIVO ,XLATLONGNAME NOMBRE  FROM XLATTABLE  WHERE FIELDNAME='" + p_nombre_campo + "' " + " AND LANGUAGE_CD='ESP' " + " AND EFF_STATUS='A'" + " ORDER BY XLATLONGNAME";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      e.printStackTrace();
      System.out.println("Error: en la consulta de datosParaListas.getOtrosDatos " + e.toString());
    }

    return datos;
  }

  public static Vector getIdiomas()
  {
    Vector datos = new Vector();
    try
    {
      String consulta = "SELECT ACCOMPLISHMENT  CODIGO , DESCR IDIOMA FROM PS_ACCOMP_TBL  WHERE ACCOMP_CATEGORY='LNG'";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de datosParaListas.getIdiomas " + e.toString());
    }

    return datos;
  }

  public static Vector getEPSS()
  {
    Vector datos = new Vector();
    try
    {
      String consulta = "SELECT ACCOMPLISHMENT  CODIGO , DESCR DESC_EPS FROM PS_ACCOMP_TBL  WHERE ACCOMP_CATEGORY='MEM'";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de datosParaListas.getEPSS " + e.toString());
    }

    return datos;
  }

  public static Vector getPeriodosInscripcion(String p_grado, String p_year)
  {
    Vector datos = new Vector();
    try
    {
      String consulta = " SELECT T.DESCRSHORT CODIGO, T.DESCR DESCRIPCION FROM PS_TERM_TBL t, PS_PUJ_PERIODOS p WHERE T.INSTITUTION= 'PUJCL' AND T.ACAD_CAREER='" + p_grado + "' " + " AND T.STRM = P.STRM" + " AND P.STRM = P.PRD_CODIGO" + " AND T.ACAD_YEAR='" + p_year + "' ";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de datosParaListas.getPeriodosInscripcion " + e.toString());
    }

    return datos;
  }

  public static Vector getConvenios()
  {
    Vector datos = new Vector();
    try
    {
      String consulta = "SELECT CODIGO, DESCRIPCION CONVENIO FROM PS_PUJ_CONVENIOS ";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de datosParaListas.getConvenios " + e.toString());
    }

    return datos;
  }

  public static Vector getActividades(String p_tipo, String p_descripcion)
  {
    Vector datos = new Vector();
    try
    {
      String consulta = "SELECT CODIGO, NOMBRE ACTIVIDAD FROM ACTIVIDADES  WHERE TAC_CODIGO = '" + p_tipo + "'" + " AND DESCRIPTORES.QUITAR_TILDES(NOMBRE) LIKE DESCRIPTORES.QUITAR_TILDES('%" + p_descripcion + "%')";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de datosParaListas.getConvenios " + e.toString());
    }

    return datos;
  }

  public static Vector getFacultades()
  {
    Vector datos = new Vector();
    try
    {
      String consulta = " SELECT ACAD_GROUP CODIGO, DESCR  DESCRIPCION  FROM SYSADM.PS_ACAD_GROUP_TBL A  WHERE EFF_STATUS='A'  AND  EFFDT = (SELECT MAX(EFFDT)  FROM SYSADM.PS_ACAD_GROUP_TBL B  WHERE A.INSTITUTION = B.INSTITUTION  AND A.ACAD_GROUP = B.ACAD_GROUP  AND B.EFFDT <= SYSDATE) ";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de datosParaListas.getFacultades " + e.toString());
    }

    return datos;
  }

  public static Vector getDepartamentosAcademicos()
  {
    Vector datos = new Vector();
    try
    {
      String consulta = " SELECT  B.ACAD_ORG CODIGO, DESCR DESCRIPCION  FROM PSTREENODE A, PS_ACAD_ORG_TBL B  WHERE A.TREE_NAME='ACAD_DEPARTMENT'  AND   A.TREE_NODE = B.DESCRSHORT  AND   B.EFF_STATUS='A'  AND   B.EFFDT = ( SELECT MAX(C.EFFDT)  FROM PS_ACAD_ORG_TBL C  WHERE B.INSTITUTION = C.INSTITUTION  AND   B.DESCRSHORT = C.DESCRSHORT  AND   C.EFFDT <= SYSDATE)  AND   A.EFFDT = (SELECT MAX(D.EFFDT)  FROM PSTREENODE D  WHERE A.TREE_NODE_NUM = D.TREE_NODE_NUM  AND  D.EFFDT <= SYSDATE)  AND  A.TREE_NODE_NUM!=1  ORDER BY A.PARENT_NODE_NAME ";

      datos = EjecutaConsulta.Ejecutar(consulta);
    } catch (Exception e) {
      System.out.println("Error: en la consulta de datosParaListas.getDepartamentosAcademicos " + e.toString());
    }

    return datos; }

  public static void main(String[] args) {
    Modelo.Path.ruta = "C:/ColeccionesPeople/WEB-INF/classes";
    try
    {
      System.out.println(getCiudades("cal", 0));
      System.out.println(getColegios("Ber", 0));
    }
    catch (Exception e)
    {
      System.out.println("errro " + e.getMessage());
    }
  }
}